-- @owner: songjing20
-- @date: 2022-6-29
-- @testpoint: 结合left join条件使用limit语法
-- @modified by: xiaqi14 2024/12/9 研发代码变更,修改时间精度

--step1:创建ustore表,插入数据;expect:成功
drop table if exists t_limit_clause_0003_01, t_limit_clause_0003_02 cascade;
create table t_limit_clause_0003_01(
COL_1 int, 
COL_2 decimal,
COL_3 smallint,
COL_4 char(30),
COL_5 real,
COL_6 numeric,
COL_7 int,
COL_8 TIMESTAMP,
COL_9 clob,
COL_10 number(6,2),
COL_11 decimal(6,2),
COL_12 varchar2(50),
COL_13 varchar(30),
COL_14 numeric(12,6),
COL_15 date,
constraint pk_limit_003_id primary key(COL_1)
) WITH (STORAGE_TYPE=USTORE);

drop sequence if exists seq_limit_clause_0003;
create sequence seq_limit_clause_0003 increment by 1 start with 100000;
begin
 for i in 1..100 loop
      insert into t_limit_clause_0003_01 values(
      seq_limit_clause_0003.nextval,
      8.3215+seq_limit_clause_0003.nextval,
      i,
      lpad('abc','30','@'),
      10.222+seq_limit_clause_0003.nextval,
      i/4,
      i,
      to_timestamp('2019-06-06 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FF'),
      lpad('abc','5000','a@123&^%djgk'),
      3.14+i,
      i+445.255,
      rpad('abc','30','&'),
      lpad('abc','30','&'),
      125563.141592,
      to_date('2019-06-07 14:58:54','YYYY-MM-DD HH24:MI:SS')
   );
 end loop;
end;
/
create table t_limit_clause_0003_02 as table t_limit_clause_0003_01;

--step3:t1表和t2表以col_1字段进行左连接，跳过前10行取20行数据，limit x,x;expect:查询成功
select t1.COL_1,t2.COL_2,t1.COL_3,t1.COL_4,t2.COL_5,t1.COL_6,t1.COL_7,t2.COL_8,t1.COL_10,t1.COL_11,t1.COL_12,t1.COL_13,t1.COL_14,t2.COL_15
from t_limit_clause_0003_01 t1 left join t_limit_clause_0003_02 t2 on t1.COL_1 = t2.COL_1 where t2.COL_2 between 100040 and 100080
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14 limit 10,20;

--step4:t1表和t2表以col_1字段进行左连接，跳过前10行取20行数据limit offset;expect:查询成功
select t1.COL_1,t2.COL_2,t1.COL_3,t1.COL_4,t2.COL_5,t1.COL_6,t1.COL_7,t2.COL_8,t1.COL_10,t1.COL_11,t1.COL_12,t1.COL_13,t1.COL_14,t2.COL_15
from t_limit_clause_0003_01 t1 left join t_limit_clause_0003_02 t2 on t1.COL_1 = t2.COL_1 where t2.COL_2 between 100040 and 100080
order by t1.COL_1,t2.COL_2,t1.COL_3,t1.COL_4,t2.COL_5,t1.COL_6,t1.COL_7,t2.COL_8,t1.COL_10,t1.COL_11,t1.COL_12,t1.COL_13,t1.COL_14,t2.COL_15 limit 20 OFFSET 10;

--step5:t1表和t2表以col_1字段进行左连接，跳过前10行取20行数据ofset limit;expect:查询成功
select t1.COL_1,t2.COL_2,t1.COL_3,t1.COL_4,t2.COL_5,t1.COL_6,t1.COL_7,t2.COL_8,t1.COL_10,t1.COL_11,t1.COL_12,t1.COL_13,t1.COL_14,t2.COL_15
from t_limit_clause_0003_01 t1 left join t_limit_clause_0003_02 t2 on t1.COL_1 = t2.COL_1 where t2.COL_2 between 100040 and 100080
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14 OFFSET 10 limit 20;

--step6:清理环境;expect:成功
drop table if exists t_limit_clause_0003_01, t_limit_clause_0003_02 cascade;
drop sequence if exists seq_limit_clause_0003;
